package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.TmsMarketCarFinancialDto;
import com.tianqu.tms.dto.TmsMarketCarFinancialParam;

public class TmsMarketCarFinancialDaoImp extends ViewListDao  implements TmsMarketCarFinancialDao{
	protected final String[] sortKeyList = new String[] {};// 列表
	
	protected TmsMarketCarFinancialDaoImp(int pageNo, int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	protected TmsMarketCarFinancialDaoImp() throws TmsDaoException {
		super();
	}

	@Override
	public void insert(TmsMarketCarFinancialDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_MARKET_CAR_FINCANCIAL);
			sql.append(" (  ");
			sql.append(" SERIAL,  ");
			sql.append(" FINANCIAID,  ");
			sql.append(" MEMBERID,  ");
			sql.append(" STATUS,  ");
			sql.append(" LICENSEPLATE,  ");
			sql.append(" VIN,  ");
			sql.append(" ATTESTSTATUS,  ");
			sql.append(" REPORTPHOTO,  ");
			sql.append(" VALUATIONS,  ");
			sql.append(" LOANLIMIT,  ");
			sql.append(" STARTDATE,  ");
			sql.append(" ENDDATE,  ");
			sql.append(" MATERIALIMAGES1,  ");
			sql.append(" MATERIALIMAGES2,  ");
			sql.append(" MATERIALIMAGES3,  ");
			sql.append(" REMARK,  ");
			sql.append(" UPDATEUSERID,  ");
			sql.append(" INSERTUSERID,  ");
			sql.append(" UPDATETIME,  ");
			sql.append(" INSERTTIME,  ");
			sql.append(" FINANCIAIDSERIAL, ");
			sql.append(" TEMPLICENSEPLATE ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),SYSDATE(),?,? ) ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getFinanciaId());
			ps.setString(3, dto.getMemberId());
			ps.setInt(4, dto.getStatus());
			ps.setString(5, dto.getLicensePlate());
			ps.setString(6, dto.getVin());
			ps.setInt(7, dto.getAttestStatus());
			ps.setString(8, dto.getReportPhoto());
			ps.setInt(9, dto.getValuations());
			ps.setInt(10, dto.getLoanLimit());
			ps.setString(11, dto.getStartDate());
			ps.setString(12, dto.getEndDate());
			ps.setString(13, dto.getMaterialImages1());
			ps.setString(14, dto.getMaterialImages2());
			ps.setString(15, dto.getMaterialImages3());
			ps.setString(16, dto.getRemark());
			ps.setInt(17, dto.getUpdateUserId());
			ps.setInt(18, dto.getInsertUserId());
			ps.setString(19, dto.getFinanciaIdSerial());
			ps.setString(20, dto.getTempLicensePlate());
			ps.executeUpdate();	
			
		} catch (SQLException e) {
			logger.error("TmsMarketCarFinancialDaoImp.insert error",e);
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
	}

	@Override
	public void update(TmsMarketCarFinancialDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_FINCANCIAL);
		sql.append(" SET ");
		sql.append(" SERIAL=").append(StringUtil.isNotBlank(dto.getSerial()) ? "?" : "SERIAL").append(",");
		sql.append(" FINANCIAID=").append(StringUtil.isNotBlank(dto.getFinanciaId()) ? "?" : "FINANCIAID").append(",");
		sql.append(" MEMBERID=").append(StringUtil.isNotBlank(dto.getMemberId()) ? "?" : "MEMBERID").append(",");
		sql.append(" STATUS=").append(dto.getStatus() > 0 ? "?" : "STATUS").append(",");
		sql.append(" LICENSEPLATE=").append(StringUtil.isNotBlank(dto.getLicensePlate()) ? "?" : "LICENSEPLATE").append(",");
		sql.append(" VIN=").append(StringUtil.isNotBlank(dto.getVin()) ? "?" : "VIN").append(",");
		sql.append(" ATTESTSTATUS=").append(dto.getAttestStatus() >0 ? "?" : "ATTESTSTATUS").append(",");
		sql.append(" REPORTPHOTO=").append(StringUtil.isNotBlank(dto.getReportPhoto()) ? "?" : "REPORTPHOTO").append(",");
		sql.append(" VALUATIONS=").append(dto.getValuations()>0 ? "?" : "VALUATIONS").append(",");
		sql.append(" LOANLIMIT=").append(dto.getLoanLimit()>0 ? "?" : "LOANLIMIT").append(",");
		sql.append(" STARTDATE=").append(StringUtil.isNotBlank(dto.getStartDate()) ? "?" : "STARTDATE").append(",");
		sql.append(" ENDDATE=").append(StringUtil.isNotBlank(dto.getEndDate()) ? "?" : "ENDDATE").append(",");
		sql.append(" MATERIALIMAGES1=").append(StringUtil.isNotBlank(dto.getMaterialImages1()) ? "?" : "MATERIALIMAGES1").append(",");
		sql.append(" MATERIALIMAGES2=").append(StringUtil.isNotBlank(dto.getMaterialImages2()) ? "?" : "MATERIALIMAGES2").append(",");
		sql.append(" MATERIALIMAGES3=").append(StringUtil.isNotBlank(dto.getMaterialImages3()) ? "?" : "MATERIALIMAGES3").append(",");
		sql.append(" REMARK=").append(StringUtil.isNotBlank(dto.getRemark()) ? "?" : "REMARK").append(",");
		sql.append(" UPDATEUSERID=").append(dto.getUpdateUserId() >0 ? "?" : "UPDATEUSERID").append(",");
		sql.append(" TEMPLICENSEPLATE=").append(StringUtil.isNotBlank(dto.getTempLicensePlate()) ? "?" : "TEMPLICENSEPLATE").append(",");
		sql.append(" UPDATETIME=SYSDATE()");
		sql.append(" WHERE ");
		sql.append(" ID=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			if(StringUtil.isNotBlank(dto.getSerial())) ps.setString(++i, dto.getSerial());
			if(StringUtil.isNotBlank(dto.getFinanciaId())) ps.setString(++i, dto.getFinanciaId());
			if(StringUtil.isNotBlank(dto.getMemberId())) ps.setString(++i, dto.getMemberId());
			if(dto.getStatus() > 0) ps.setInt(++i, dto.getStatus());
			if(StringUtil.isNotBlank(dto.getLicensePlate())) ps.setString(++i, dto.getLicensePlate());
			if(StringUtil.isNotBlank(dto.getVin())) ps.setString(++i, dto.getVin());
			if(dto.getAttestStatus() > 0) ps.setInt(++i, dto.getAttestStatus());
			if(StringUtil.isNotBlank(dto.getReportPhoto())) ps.setString(++i, dto.getReportPhoto());
			if(dto.getValuations() > 0) ps.setInt(++i, dto.getValuations());
			if(dto.getLoanLimit() > 0) ps.setInt(++i, dto.getLoanLimit());
			if(StringUtil.isNotBlank(dto.getStartDate())) ps.setString(++i, dto.getStartDate());
			if(StringUtil.isNotBlank(dto.getEndDate())) ps.setString(++i, dto.getEndDate());
			if(StringUtil.isNotBlank(dto.getMaterialImages1())) ps.setString(++i, dto.getMaterialImages1());
			if(StringUtil.isNotBlank(dto.getMaterialImages2())) ps.setString(++i, dto.getMaterialImages2());
			if(StringUtil.isNotBlank(dto.getMaterialImages3())) ps.setString(++i, dto.getMaterialImages3());
			if(StringUtil.isNotBlank(dto.getRemark())) ps.setString(++i, dto.getRemark());
			if(dto.getUpdateUserId() > 0) ps.setInt(++i, dto.getUpdateUserId());
			if(StringUtil.isNotBlank(dto.getTempLicensePlate())) ps.setString(++i, dto.getTempLicensePlate());
			ps.setLong(++i, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			logger.error("TmsMarketCarFinancialDaoImp.update error",e);
			throw new TmsDaoException(e.toString());
		}finally {
			super.close(rs, ps);
		}
	}
	
	@Override
	public TmsMarketCarFinancialDto get(String id) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsMarketCarFinancialDto dto = null;
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append(" SELECT ");
			sql.append(" FINCANCIAL_CAR.ID,  ");
			sql.append(" FINCANCIAL_CAR.SERIAL,  ");
			sql.append(" FINCANCIAL_CAR.FINANCIAIDSERIAL,  ");
			sql.append(" FINCANCIAL_CAR.FINANCIAID,  ");
			sql.append(" FINCANCIAL_CAR.MEMBERID,  ");
			sql.append(" FINCANCIAL_CAR.STATUS,  ");
			sql.append(" FINCANCIAL_CAR.LICENSEPLATE,  ");
			sql.append(" FINCANCIAL_CAR.VIN,  ");
			sql.append(" FINCANCIAL_CAR.ATTESTSTATUS,  ");
			sql.append(" FINCANCIAL_CAR.REPORTPHOTO,  ");
			sql.append(" FINCANCIAL_CAR.VALUATIONS,  ");
			sql.append(" FINCANCIAL_CAR.LOANLIMIT,  ");
			sql.append(" FINCANCIAL_CAR.STARTDATE,  ");
			sql.append(" FINCANCIAL_CAR.ENDDATE,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES1,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES2,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES3,  ");
			sql.append(" FINCANCIAL_CAR.REMARK,  ");
			sql.append(" FINCANCIAL_CAR.UPDATEUSERID,  ");
			sql.append(" FINCANCIAL_CAR.INSERTUSERID,  ");
			sql.append(" FINCANCIAL_CAR.UPDATETIME,  ");
			sql.append(" FINCANCIAL_CAR.INSERTTIME,  ");
			sql.append(" FINANCIA.SHORTNAME AS FINANCIANAME,  ");
			sql.append(" MEMBER.SHORTNAME AS MEMBERNAME,  ");
			sql.append(" CAR.REGISTERSTATUS,  ");
			sql.append(" CAR.BARCODENUM  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CAR_FINCANCIAL).append(" AS FINCANCIAL_CAR LEFT JOIN ");
			sql.append(" "+TmsTableNames.TMS_MARKET_CAR_INFO+" AS CAR ON(FINCANCIAL_CAR.SERIAL = CAR.SERIAL)");
			sql.append(" LEFT JOIN "+TmsTableNames.TMS_FINANCIA+" AS FINANCIA ON(FINCANCIAL_CAR.FINANCIAID = FINANCIA.FINANCIAID)");
			sql.append(" LEFT JOIN "+TmsTableNames.TMS_MEMBER+" AS MEMBER ON(FINCANCIAL_CAR.MEMBERID = MEMBER.MEMBERID)");
			sql.append(" WHERE 1=1");
			sql.append(" AND FINCANCIAL_CAR.ID = '" + id + "'");
			 
			sql.append(getOrderBySql(sortKeyList));
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new TmsMarketCarFinancialDto();
				dto.setId(rs.getInt("ID"));
				dto.setFinanciaName(rs.getString("FINANCIANAME"));
				dto.setMemberName(rs.getString("MEMBERNAME"));
				dto.setFinanciaIdSerial(rs.getString("FINANCIAIDSERIAL"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinanciaId(rs.getString("FINANCIAID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setVin(rs.getString("VIN"));
				dto.setAttestStatus(rs.getInt("ATTESTSTATUS"));
				dto.setReportPhoto(rs.getString("REPORTPHOTO"));
				dto.setValuations(rs.getInt("VALUATIONS"));
				dto.setLoanLimit(rs.getInt("LOANLIMIT"));
				dto.setStartDate(DateUtil.getDateByTimeStamp(rs.getTimestamp("STARTDATE")));
				dto.setEndDate(DateUtil.getDateByTimeStamp(rs.getTimestamp("ENDDATE")));
				dto.setMaterialImages1(rs.getString("MATERIALIMAGES1"));
				dto.setMaterialImages2(rs.getString("MATERIALIMAGES2"));
				dto.setMaterialImages3(rs.getString("MATERIALIMAGES3"));
				dto.setRemark(rs.getString("REMARK"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getString("UPDATETIME"));
				dto.setInsertTime(rs.getString("INSERTTIME"));
				dto.setRegisterStatus(rs.getInt("REGISTERSTATUS"));
				dto.setBarCodeNum(rs.getInt("BARCODENUM"));
			}
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.find error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	public List<TmsMarketCarFinancialDto> find(TmsMarketCarFinancialParam param) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TmsMarketCarFinancialDto> list = new ArrayList<TmsMarketCarFinancialDto>();
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append(" SELECT ");
			sql.append(" FINCANCIAL_CAR.ID,  ");
			sql.append(" FINCANCIAL_CAR.TEMPLICENSEPLATE,  ");
			sql.append(" FINCANCIAL_CAR.SERIAL,  ");
			sql.append(" FINCANCIAL_CAR.FINANCIAIDSERIAL,  ");
			sql.append(" FINCANCIAL_CAR.FINANCIAID,  ");
			sql.append(" FINCANCIAL_CAR.MEMBERID,  ");
			sql.append(" FINCANCIAL_CAR.STATUS,  ");
			sql.append(" FINCANCIAL_CAR.LICENSEPLATE,  ");
			sql.append(" FINCANCIAL_CAR.VIN,  ");
			sql.append(" FINCANCIAL_CAR.ATTESTSTATUS,  ");
			sql.append(" FINCANCIAL_CAR.REPORTPHOTO,  ");
			sql.append(" FINCANCIAL_CAR.VALUATIONS,  ");
			sql.append(" FINCANCIAL_CAR.LOANLIMIT,  ");
			sql.append(" FINCANCIAL_CAR.STARTDATE,  ");
			sql.append(" FINCANCIAL_CAR.ENDDATE,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES1,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES2,  ");
			sql.append(" FINCANCIAL_CAR.MATERIALIMAGES3,  ");
			sql.append(" FINCANCIAL_CAR.REMARK,  ");
			sql.append(" FINCANCIAL_CAR.UPDATEUSERID,  ");
			sql.append(" FINCANCIAL_CAR.INSERTUSERID,  ");
			sql.append(" FINCANCIAL_CAR.UPDATETIME,  ");
			sql.append(" FINCANCIAL_CAR.INSERTTIME,  ");
			sql.append(" FINANCIA.SHORTNAME AS FINANCIANAME,  ");
			sql.append(" MEMBER.SHORTNAME AS MEMBERNAME,  ");
			sql.append(" CAR.REGISTERSTATUS,  ");
			sql.append(" CAR.BARCODENUM  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CAR_FINCANCIAL).append(" AS FINCANCIAL_CAR LEFT JOIN ");
			sql.append(" "+TmsTableNames.TMS_MARKET_CAR_INFO+" AS CAR ON(FINCANCIAL_CAR.SERIAL = CAR.SERIAL)");
			sql.append(" LEFT JOIN "+TmsTableNames.TMS_FINANCIA+" AS FINANCIA ON(FINCANCIAL_CAR.FINANCIAID = FINANCIA.FINANCIAID)");
			sql.append(" LEFT JOIN "+TmsTableNames.TMS_MEMBER+" AS MEMBER ON(FINCANCIAL_CAR.MEMBERID = MEMBER.MEMBERID)");
			sql.append(" WHERE 1=1");
			if (!StringUtil.isNullOrBlank(param.getId())) {
				sql.append(" AND FINCANCIAL_CAR.ID = '" + param.getId() + "'");
			}
			if (!StringUtil.isNullOrBlank(param.getMemberId())) {
				sql.append(" AND FINCANCIAL_CAR.MEMBERID = '" + param.getMemberId() + "'");
			}
			if (!StringUtil.isNullOrBlank(param.getFinanciaId())) {
				sql.append(" AND FINCANCIAL_CAR.FINANCIAID = '" + param.getFinanciaId() + "'");
			}
			if (!StringUtil.isNullOrBlank(param.getVin())) {
				if(param.isFuzzy()) {
					sql.append(" AND FINCANCIAL_CAR.VIN LIKE '%" + param.getVin() + "'");
				}else {
					sql.append(" AND FINCANCIAL_CAR.VIN = '" + param.getVin() + "'");
				}
				
			}
			if (!StringUtil.isNullOrBlank(param.getLicensePlate())) {
				if(param.isFuzzy()) {
					sql.append(" AND FINCANCIAL_CAR.LICENSEPLATE LIKE '%" + param.getLicensePlate() + "'");
				}else {
					sql.append(" AND FINCANCIAL_CAR.LICENSEPLATE = '" + param.getLicensePlate() + "'");
				}
				
			}
			if (!StringUtil.isNullOrBlank(param.getTempLicensePlate())) {
				sql.append(" AND FINCANCIAL_CAR.TEMPLICENSEPLATE = '" + param.getTempLicensePlate() + "'");
			}
			if (param.getStatus() > 0) {
				sql.append(" AND FINCANCIAL_CAR.STATUS = '" + param.getStatus() + "'");
			}
			// 排序方法
			sql.append("ORDER BY FINCANCIAL_CAR.updateTime DESC");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				TmsMarketCarFinancialDto dto = new TmsMarketCarFinancialDto();
				dto.setId(rs.getInt("ID"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setFinanciaName(rs.getString("FINANCIANAME"));
				dto.setMemberName(rs.getString("MEMBERNAME"));
				dto.setFinanciaIdSerial(rs.getString("FINANCIAIDSERIAL"));
				dto.setSerial(rs.getString("SERIAL"));
				dto.setFinanciaId(rs.getString("FINANCIAID"));
				dto.setMemberId(rs.getString("MEMBERID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setVin(rs.getString("VIN"));
				dto.setAttestStatus(rs.getInt("ATTESTSTATUS"));
				dto.setReportPhoto(rs.getString("REPORTPHOTO"));
				dto.setValuations(rs.getInt("VALUATIONS"));
				dto.setLoanLimit(rs.getInt("LOANLIMIT"));
				dto.setStartDate(DateUtil.getDateByTimeStamp(rs.getTimestamp("STARTDATE")));
				dto.setEndDate(DateUtil.getDateByTimeStamp(rs.getTimestamp("ENDDATE")));
				dto.setMaterialImages1(rs.getString("MATERIALIMAGES1"));
				dto.setMaterialImages2(rs.getString("MATERIALIMAGES2"));
				dto.setMaterialImages3(rs.getString("MATERIALIMAGES3"));
				dto.setRemark(rs.getString("REMARK"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getString("UPDATETIME"));
				dto.setInsertTime(rs.getString("INSERTTIME"));
				dto.setRegisterStatus(rs.getInt("REGISTERSTATUS"));
				dto.setBarCodeNum(rs.getInt("BARCODENUM"));
				list.add(dto);
			}
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.find error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return list;
	}
}
